- frmCourseFeePaymentRecord.vb
- project /
1 Imports System.Data.SqlClient
2 Imports System.IO
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmCourseFeePaymentRecord
5 Public Sub GetData()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 cmd = New SqlCommand("Select RTRIM(CourseFeePayment.Id) as [ID], RTRIM(CFP_ID) as [CFP ID], RTRIM(PaymentID) as [Payment ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(CourseFeePayment.Class) as [Class],RTRIM(CourseFeePayment.Section) as [Section], RTRIM(CourseFeePayment.Session) as [Session], RTRIM(Semester) as [Semester], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(CourseFeePayment.ClassType) as [Class Type], RTRIM(CourseFeePayment.SchoolType) as [School Type] from Student,Class,Section,SchoolInfo,CourseFeePayment where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=CourseFeePayment.AdmissionNo order by StudentName", con)
10 adp = New SqlDataAdapter(cmd)
11 ds = New DataSet()
12 adp.Fill(ds, "Student")
13 dgw.DataSource = ds.Tables("Student").DefaultView
14 con.Close()
15 Catch ex As Exception
16 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
17 End Try
18 End Sub
19
20 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
21 Me.Close()
22 End Sub
23
24 Private Sub txtStudentName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStudentName.TextChanged
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 cmd = New SqlCommand("Select RTRIM(CourseFeePayment.Id) as [ID], RTRIM(CFP_ID) as [CFP ID], RTRIM(PaymentID) as [Payment ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(CourseFeePayment.Class) as [Class],RTRIM(CourseFeePayment.Section) as [Section], RTRIM(CourseFeePayment.Session) as [Session], RTRIM(Semester) as [Semester], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(CourseFeePayment.ClassType) as [Class Type], RTRIM(CourseFeePayment.SchoolType) as [School Type] from Student,Class,Section,SchoolInfo,CourseFeePayment where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=CourseFeePayment.AdmissionNo and StudentName like '" & txtStudentName.Text & "%' order by StudentName", con)
29 adp = New SqlDataAdapter(cmd)
30 ds = New DataSet()
31 adp.Fill(ds, "Student")
32 dgw.DataSource = ds.Tables("Student").DefaultView
33 con.Close()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
40 Try
41 con = New SqlConnection(cs)
42 con.Open()
43 cmd = New SqlCommand("Select RTRIM(CourseFeePayment.Id) as [ID], RTRIM(CFP_ID) as [CFP ID], RTRIM(PaymentID) as [Payment ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(CourseFeePayment.Class) as [Class],RTRIM(CourseFeePayment.Section) as [Section], RTRIM(CourseFeePayment.Session) as [Session], RTRIM(Semester) as [Semester], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(CourseFeePayment.ClassType) as [Class Type], RTRIM(CourseFeePayment.SchoolType) as [School Type] from Student,Class,Section,SchoolInfo,CourseFeePayment where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=CourseFeePayment.AdmissionNo and CourseFeePayment.Session=@d1 and CourseFeePayment.Class=@d2 order by StudentName", con)
44 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
45 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
46 adp = New SqlDataAdapter(cmd)
47 ds = New DataSet()
48 adp.Fill(ds, "Student")
49 dgw.DataSource = ds.Tables("Student").DefaultView
50 con.Close()
51 Catch ex As Exception
52 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
53 End Try
54 End Sub
55
56 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
57 Try
58 con = New SqlConnection(cs)
59 con.Open()
60 cmd = New SqlCommand("Select RTRIM(CourseFeePayment.Id) as [ID], RTRIM(CFP_ID) as [CFP ID], RTRIM(PaymentID) as [Payment ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(CourseFeePayment.Class) as [Class],RTRIM(CourseFeePayment.Section) as [Section], RTRIM(CourseFeePayment.Session) as [Session], RTRIM(Semester) as [Semester], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(CourseFeePayment.ClassType) as [Class Type], RTRIM(CourseFeePayment.SchoolType) as [School Type] from Student,Class,Section,SchoolInfo,CourseFeePayment where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=CourseFeePayment.AdmissionNo and PaymentDate between @d1 and @d2 order by StudentName", con)
61 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
62 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value
63 adp = New SqlDataAdapter(cmd)
64 ds = New DataSet()
65 adp.Fill(ds, "Student")
66 dgw.DataSource = ds.Tables("Student").DefaultView
67 con.Close()
68 Catch ex As Exception
69 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
70 End Try
71 End Sub
72
73 Sub fillSession()
74 Try
75 con = New SqlConnection(cs)
76 con.Open()
77 adp = New SqlDataAdapter()
78 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM CourseFeepayment", con)
79 ds = New DataSet("ds")
80 adp.Fill(ds)
81 dtable = ds.Tables(0)
82 cmbSession.Items.Clear()
83 For Each drow As DataRow In dtable.Rows
84 cmbSession.Items.Add(drow(0).ToString())
85 Next
86 Catch ex As Exception
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
88 End Try
89 End Sub
90
91 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
92 Try
93 cmbClass.Enabled = True
94 con = New SqlConnection(cs)
95 con.Open()
96 Dim ct As String = "SELECT distinct RTRIM(Class) FROM CourseFeePayment where CourseFeePayment.Session=@d1"
97 cmd = New SqlCommand(ct)
98 cmd.Connection = con
99 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
100 rdr = cmd.ExecuteReader()
101 cmbClass.Items.Clear()
102 While rdr.Read
103 cmbClass.Items.Add(rdr(0))
104 End While
105 con.Close()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109
110 End Sub
111
112
113 Private Sub txtAdmissionNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtAdmissionNo.TextChanged
114 Try
115 con = New SqlConnection(cs)
116 con.Open()
117 cmd = New SqlCommand("Select RTRIM(CourseFeePayment.Id) as [ID], RTRIM(CFP_ID) as [CFP ID], RTRIM(PaymentID) as [Payment ID], RTRIM(Student.AdmissionNo) as [Admission No.],RTRIM(StudentName) as [StudentName],RTRIM(EnrollmentNo) as [Enrollment No.],RTRIM(SchoolName) as [School Name],RTRIM(CourseFeePayment.Class) as [Class],RTRIM(CourseFeePayment.Section) as [Section], RTRIM(CourseFeePayment.Session) as [Session], RTRIM(Semester) as [Semester], RTRIM(TotalFee) as [Total Fee], RTRIM(DiscountPer) as [Discount %], RTRIM(DiscountAmt) as [Discount], RTRIM(PreviousDue) as [Previous Due], RTRIM(Fine) as [Fine], RTRIM(GrandTotal) as [Grand Total], RTRIM(TotalPaid) as [Total Paid], RTRIM(ModeOfPayment) as [Payment Mode], RTRIM(PaymentModeDetails) as [Payement Mode Info], Convert(Datetime,PaymentDate,131) as [Payement Date], RTRIM(PaymentDue) as [Payement Due],RTRIM(CourseFeePayment.ClassType) as [Class Type], RTRIM(CourseFeePayment.SchoolType) as [School Type] from Student,Class,Section,SchoolInfo,CourseFeePayment where Student.SectionID=Section.ID and Class.ClassName=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Student.AdmissionNo=CourseFeePayment.AdmissionNo and Student.AdmissionNo like '" & txtAdmissionNo.Text & "%' order by StudentName", con)
118 adp = New SqlDataAdapter(cmd)
119 ds = New DataSet()
120 adp.Fill(ds, "Student")
121 dgw.DataSource = ds.Tables("Student").DefaultView
122 con.Close()
123 Catch ex As Exception
124 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
125 End Try
126 End Sub
127 Sub Reset()
128 txtAdmissionNo.Text = ""
129 txtStudentName.Text = ""
130 cmbClass.SelectedIndex = -1
131 cmbSession.SelectedIndex = -1
132 cmbClass.Enabled = False
133 dtpDateFrom.Text = Today
134 dtpDateTo.Text = Now
135 GetData()
136 End Sub
137 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
138 Reset()
139 End Sub
140
141 Private Sub frmStudentRecord_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
142 fillSession()
143 GetData()
144 End Sub
145
146 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
147 Try
148 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
149 If lblSet.Text = "Course Fee Payment" Then
150 Me.Hide()
151 frmCourseFeePayment.Show()
152 frmCourseFeePayment.txtID.Text = dr.Cells(0).Value.ToString()
153 frmCourseFeePayment.txtCFPId.Text = dr.Cells(1).Value.ToString()
154 frmCourseFeePayment.txtFeePaymentID.Text = dr.Cells(2).Value.ToString()
155 frmCourseFeePayment.txtAdmissionNo.Text = dr.Cells(3).Value.ToString()
156 frmCourseFeePayment.txtStudentName.Text = dr.Cells(4).Value.ToString()
157 frmCourseFeePayment.txtEnrollmentNo.Text = dr.Cells(5).Value.ToString() '
158 frmCourseFeePayment.txtSchoolName.Text = dr.Cells(6).Value.ToString()
159 frmCourseFeePayment.txtClass.Text = dr.Cells(7).Value.ToString()
160 frmCourseFeePayment.txtSection.Text = dr.Cells(8).Value.ToString()
161 frmCourseFeePayment.txtSession.Text = dr.Cells(9).Value.ToString()
162 frmCourseFeePayment.cmbSemester.Text = dr.Cells(10).Value.ToString()
163 frmCourseFeePayment.txtCourseFee.Text = dr.Cells(11).Value.ToString()
164 frmCourseFeePayment.txtDiscountPer.Text = dr.Cells(12).Value.ToString()
165 frmCourseFeePayment.txtDiscount.Text = dr.Cells(13).Value.ToString()
166 frmCourseFeePayment.txtPreviousDue.Text = dr.Cells(14).Value.ToString()
167 frmCourseFeePayment.txtFine.Text = dr.Cells(15).Value.ToString()
168 frmCourseFeePayment.txtGrandTotal.Text = dr.Cells(16).Value.ToString()
169 frmCourseFeePayment.txtTotalPaid.Text = dr.Cells(17).Value.ToString()
170 frmCourseFeePayment.cmbPaymentMode.Text = dr.Cells(18).Value.ToString()
171 frmCourseFeePayment.txtPaymentModeDetails.Text = dr.Cells(19).Value.ToString()
172 frmCourseFeePayment.dtpPaymentDate.Text = dr.Cells(20).Value.ToString()
173 frmCourseFeePayment.txtBalance.Text = dr.Cells(21).Value.ToString()
174 frmCourseFeePayment.txtClassType.Text = dr.Cells(22).Value.ToString()
175 frmCourseFeePayment.txtSchoolType.Text = dr.Cells(23).Value.ToString()
176 con = New SqlConnection(cs)
177 con.Open()
178 cmd = New SqlCommand("SELECT FeeName,CourseFeePayment_Join.Fee from CourseFeePayment,CourseFeePayment_Join where CourseFeePayment.ID=CourseFeePayment_Join.C_PaymentID and CourseFeePayment.ID=" & dr.Cells(0).Value & "", con)
179 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
180 frmCourseFeePayment.dgw.Rows.Clear()
181 While (rdr.Read() = True)
182 frmCourseFeePayment.dgw.Rows.Add(rdr(0), rdr(1))
183 End While
184 con.Close()
185 frmCourseFeePayment.btnDelete.Enabled = True
186 frmCourseFeePayment.btnUpdate.Enabled = True
187 frmCourseFeePayment.btnSave.Enabled = False
188 frmCourseFeePayment.Button2.Enabled = False
189 frmCourseFeePayment.dtpPaymentDate.Enabled = False
190 frmCourseFeePayment.btnPrint.Enabled = True
191 lblSet.Text = ""
192 End If
193
194 Catch ex As Exception
195 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
196 End Try
197 End Sub
198
199 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
200 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
201 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
202 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
203 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
204 End If
205 Dim b As Brush = SystemBrushes.ControlText
206 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
207
208 End Sub
209
210 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
211 Dim rowsTotal, colsTotal As Short
212 Dim I, j, iC As Short
213 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
214 Dim xlApp As New Excel.Application
215 Try
216 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
217 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
218 xlApp.Visible = True
219
220 rowsTotal = dgw.RowCount
221 colsTotal = dgw.Columns.Count - 1
222 With excelWorksheet
223 .Cells.Select()
224 .Cells.Delete()
225 For iC = 0 To colsTotal
226 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
227 Next
228 For I = 0 To rowsTotal - 1
229 For j = 0 To colsTotal
230 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
231 Next j
232 Next I
233 .Rows("1:1").Font.FontStyle = "Bold"
234 .Rows("1:1").Font.Size = 12
235
236 .Cells.Columns.AutoFit()
237 .Cells.Select()
238 .Cells.EntireColumn.AutoFit()
239 .Cells(1, 1).Select()
240 End With
241 Catch ex As Exception
242 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
243 Finally
244 'RELEASE ALLOACTED RESOURCES
245 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
246 xlApp = Nothing
247 End Try
248 End Sub
249 End Class
